/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import model.entity.*;

public class AccountImp {
   
    public boolean Login(String username,String password,int role)
    {
        boolean kq = true;
        try {
             
             Connection con = ConnectDatabase.getConnection();
             PreparedStatement prst = con.prepareStatement("select * from account where username = ? and password = ? and role = ?");
             prst.setString(1, username);
             prst.setString(2, password);
             prst.setInt(3, role);

            ResultSet rs = prst.executeQuery();
            kq = rs.next();
          
        } catch (Exception e) {
            kq = false;
        }
        return kq;
    }
    public boolean CreateAccountadm(String username,String password,String firstname,String lastname,String country, String city,String state_province,String street, String homephone, String mobile, String typecard, String cardnumber,  String cardexpiry, int role, String email, double credit)
    {
        boolean kq = true;
        try
        {
            Connection con = ConnectDatabase.getConnection();
            PreparedStatement prst = con.prepareCall("insert into account values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            prst.setString(1, username);
            prst.setString(2, password);
            prst.setString(3, firstname);
            prst.setString(4, lastname);
            prst.setString(5, country);
            prst.setString(6, city);
            prst.setString(7, state_province);
            prst.setString(8, street);
            prst.setString(9, homephone);
            prst.setString(10, mobile);
            prst.setString(11, typecard);
            prst.setString(12, cardnumber);
            prst.setString(13, cardexpiry);
            prst.setInt(14, role);
            prst.setString(15, email);
            prst.setDouble(16, credit);
            prst.execute();
            prst.close();
        } 
        catch (Exception e){
            kq = false;
        }
            return kq;
    }
    
    
    public boolean EditAccount(String username,String password,String firstname,String lastname,String country, String city,String state_province,String street, String homephone, String mobile, String typecard, String cardnumber,  String cardexpiry, String email)
    {
        boolean kq = true;
        try
        {
            Connection con = ConnectDatabase.getConnection();
            PreparedStatement prst = con.prepareCall("update account set password = ?, firstname = ?, lastname = ?, country = ?, city = ?, state_province = ?, street = ?, homephone = ?, mobile = ?, typecard = ?, cardnumber = ?, cardexpiry = ?, email = ? where username = ?");
            prst.setString(14, username);
            prst.setString(1, password);
            prst.setString(2, firstname);
            prst.setString(3, lastname);
            prst.setString(4, country);
            prst.setString(5, city);
            prst.setString(6, state_province);
            prst.setString(7, street);
            prst.setString(8, homephone);
            prst.setString(9, mobile);
            prst.setString(10, typecard);
            prst.setString(11, cardnumber);
            prst.setString(12, cardexpiry);
            prst.setString(13, email);
            
            prst.execute();
            prst.close();
        } 
        catch (Exception e){
            kq = false;
        }
            return kq;
    }
    public boolean DeleteAccount(String username,int role)
    {
        boolean kq = true;
        try {
            Connection con = ConnectDatabase.getConnection();
            PreparedStatement prst = con.prepareCall("delete from account where username= ? and role = ?");
            prst.setString(1, username);
            prst.setInt(2, role);
            prst.execute();
            
            
        } catch (Exception e) {
            kq = false;
        }
        return kq;
    }

    public List<Account> AllAccount()
    {
        List<Account> lstAcc = new ArrayList<Account>();
        
        try {
            Statement st = ConnectDatabase.getConnection().createStatement();
            ResultSet rs = st.executeQuery("select * from account where role=2");
            while(rs.next())
            {
                lstAcc.add(new Account(rs.getString("username"), rs.getString("password"), rs.getString("firstname"), rs.getString("lastname"),rs.getString("country"), rs.getString("city"), rs.getString("state_province"), rs.getString("street"), rs.getString("homephone"), rs.getString("mobile"), rs.getString("typecard"), rs.getString("cardnumber"), rs.getString("cardexpiry"), rs.getString("email"), rs.getInt("role")));
            }
        } catch (Exception e) {
            lstAcc = null;
        }
        return lstAcc;
    }
    
    public boolean Credit(String username, double credit)
    {
        boolean kq = true;
        try {
            Connection con = ConnectDatabase.getConnection();
            PreparedStatement prst = con.prepareCall("update account set credit = ? where username = ?");
            prst.setString(2, username);
            prst.setDouble(1, credit);
            prst.execute();
            
            
        } catch (Exception e) {
            kq = false;
        }
        return kq;
    }
    
    public boolean CheckLogin(String username, String password)
    {
        boolean kq = true;
        try {
            PreparedStatement ps = ConnectDatabase.getConnection().prepareStatement("select * from account where username = ? and password = ?");
            ps.setString(1, username);
            ps.setString(2, password);
            ResultSet rs = ps.executeQuery();
            kq = rs.next();
        } catch (Exception e) {
            kq = false;
        }
        return kq;
    }
    
    public Account DetailAccbyEmail(String email)
    {
        Account acc = new Account();
        try {
            PreparedStatement ps = ConnectDatabase.getConnection().prepareStatement("select * from account where email = ?");
            ps.setString(1, email);
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                acc.setUsername(rs.getString("username"));
                acc.setPassword(rs.getString("password"));
                acc.setFirstname(rs.getString("firstname"));
                acc.setLastname(rs.getString("lastname"));
                acc.setCountry(rs.getString("country"));
                acc.setCity(rs.getString("city"));
                acc.setState_province(rs.getString("state_province"));
                acc.setStreet(rs.getString("street"));
                acc.setHomephone(rs.getString("homephone"));
                acc.setMobile(rs.getString("mobile"));
                acc.setTypecard(rs.getString("typecard"));
                acc.setCardnumber(rs.getString("cardnumber"));
                acc.setCardexpiry(rs.getString("cardexpiry"));
                acc.setRole(rs.getInt("role"));
                acc.setEmail(rs.getString("email"));
            }
            ps.close();
        } catch (Exception e) {
            acc = null;
        }
        return acc;
    }
    
    public Account DetailAcc(String username)
    {
        Account acc = new Account();
        try {
            PreparedStatement ps = ConnectDatabase.getConnection().prepareStatement("select * from account where username = ?");
            ps.setString(1, username);
            ResultSet rs = ps.executeQuery();
            while(rs.next())
            {
                acc.setUsername(rs.getString("username"));
                acc.setPassword(rs.getString("password"));
                acc.setFirstname(rs.getString("firstname"));
                acc.setLastname(rs.getString("lastname"));
                acc.setCountry(rs.getString("country"));
                acc.setCity(rs.getString("city"));
                acc.setState_province(rs.getString("state_province"));
                acc.setStreet(rs.getString("street"));
                acc.setHomephone(rs.getString("homephone"));
                acc.setMobile(rs.getString("mobile"));
                acc.setTypecard(rs.getString("typecard"));
                acc.setCardnumber(rs.getString("cardnumber"));
                acc.setCardexpiry(rs.getString("cardexpiry"));
                acc.setRole(rs.getInt("role"));
                acc.setEmail(rs.getString("email"));
                acc.setCredit(rs.getDouble("credit"));
            }
            
        } catch (Exception e) {
            acc = null;
        }
        return acc;
    }
    
    public  boolean CreateAccount(Account acc)
    {
        boolean kq = true;
        try {
            PreparedStatement ps = ConnectDatabase.getConnection().prepareStatement("insert into account values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            ps.setString(1, acc.getUsername());
            ps.setString(2, acc.getPassword());
            ps.setString(3, acc.getFirstname());
            ps.setString(4, acc.getLastname());
            ps.setString(5, acc.getCountry());
            ps.setString(6, acc.getCity());
            ps.setString(7, acc.getState_province());
            ps.setString(8, acc.getStreet());
            ps.setString(9, acc.getHomephone());
            ps.setString(10, acc.getMobile());
            ps.setString(11, acc.getTypecard());
            ps.setString(12, acc.getCardnumber());
            ps.setString(13, acc.getCardexpiry());
            ps.setInt(14, acc.getRole());
            ps.setString(15, acc.getEmail());
            ps.setDouble(16, acc.getCredit());
            kq = ps.executeUpdate()>0;
        } catch (Exception e) { 
            kq = false;
        }
        return kq;
    }
    
    public boolean ChangePass(String password, String firstname, String lastname)
    {
        boolean kq = true;
        try {
            Connection con = ConnectDatabase.getConnection();
            PreparedStatement ps = con.prepareCall("update account set password = ?, firstname = ?, lastname = ?");
            ps.setString(1, password);
            ps.setString(2, firstname);
            ps.setString(3, lastname);
           kq = ps.executeUpdate()>0;
        } catch (Exception e) {
            kq = false;
        }
        return kq;
    }
    public boolean ChangeinfoAccount(String username, String firstname, String lastname, String country, String city, String state_province, String street, String mobile, String homephone)
    {
        boolean kq = true;
        try {
            Connection con = ConnectDatabase.getConnection();
            PreparedStatement ps = con.prepareCall("update account set firstname = ?, lastname = ?, country = ?, city = ?, state_province = ?, street = ?, mobile = ?, homephone = ? where username = ? ");
            ps.setString(9, username);
            ps.setString(1, firstname);
            ps.setString(2, lastname);
            ps.setString(3, country);
            ps.setString(4, city);
            ps.setString(5, state_province);
            ps.setString(6, street);
            ps.setString(7, mobile);
            ps.setString(8, homephone);
            kq = ps.executeUpdate()>0;
           
        } catch (Exception e) {
            kq = false;
        }
        return kq;
    }
    public boolean Checkusername_pass(String username, String password)
    {
        boolean kq = true;
        try {
            PreparedStatement ps = ConnectDatabase.getConnection().prepareStatement("select * from account where username = ? and password = ?");
            ps.setString(1, username);
            ps.setString(2, password);
            ResultSet rs = ps.executeQuery();
            kq = rs.next();
        } catch (Exception e) {
            kq = false;
        }
        return kq;
    }
    public boolean Checkemail(String email)
    {
        boolean kq = true;
        try {
            PreparedStatement ps = ConnectDatabase.getConnection().prepareStatement("select * from account where email = ?");
            ps.setString(1, email);
            ResultSet rs = ps.executeQuery();
            kq = rs.next();
            
        } catch (Exception e) {
            kq = false;
        }
        return kq;
    }
    
    public boolean Checkusername_email(String username, String email)
    {
        boolean kq = true;
        try {
            PreparedStatement ps = ConnectDatabase.getConnection().prepareStatement("select * from account where username = ? and email = ? ");
            ps.setString(1, username);
            ps.setString(2, email);
            ResultSet rs = ps.executeQuery();
            kq = rs.next();
            
        } catch (Exception e) {
            kq = false;
        }
        return kq;
    }
    
    public boolean addnewcredit(String username, double credit)
    {
        boolean kq = true;
        try {
            Connection con = ConnectDatabase.getConnection();
            PreparedStatement ps = con.prepareCall("update account set credit = ? where username = ? ");
            ps.setString(2, username);
            ps.setDouble(1, credit);
            kq = ps.executeUpdate()>0;
        } catch (Exception e) {
            kq = false;
        }
        return kq;
    }
    
    
    
    
}
